-- 上机1
/*
===========================================================
|              创建一个自动增长的表空间tp_orders
============================================================
*/
CREATE TABLESPACE tp_orders
DATAFILE 'D:\ORADATA\Data\working01.DBF'
SIZE 10M AUTOEXTEND ON;

/*
===========================================================
|              创建A_od用户
============================================================
*/
CREATE USER A_od
IDENTIFIED BY bdqn
DEFAULT TABLESPACE tp_orders
TEMPORARY TABLESPACE temp;

/*
============================================================
|         将A_hr. employee表的访问权限授予A_oe用户 
============================================================
*/
GRANT connect,resource TO A_od;	 -- 授权
GRANT SELECT ON scott.employee TO A_od;	-- 允许用户查看employee表中的记录


-- 上机2
/*
===========================================================
|             使用序列生成部门表中部门编号的值
============================================================
*/
CREATE SEQUENCE dept_seq
START WITH 60
INCREMENT BY 10
MAXVALUE 10000
NOCYCLE
CACHE 30;

/*
===========================================================
|           创建dept表
============================================================
*/
CREATE TABLE dept
(
	deptId NUMBER(10) NOT NULL,
	jobType VARCHAR2(20) NOT NULL,
	jobName VARCHAR2(20) NOT NULL
);

/*
===========================================================
|           插入两条记录
============================================================
*/
INSERT INTO dept VALUES(dept_seq.nextval,'PERSONNEL','BEIJING');
INSERT INTO dept VALUES(dept_seq.nextval,'MARKET','SHANGHAI');

-- 数据库迁移前准备工作(备份)
DROP SEQUENCE dept_seq;

-- 恢复序列对象
CREATE SEQUENCE dept_seq
START WITH 60
INCREMENT BY 10
MAXVALUE 10000
NOCYCLE
CACHE 30;

-- 创建deptBak部门表
CREATE TABLE deptBak AS SELECT * FROM dept;

-- 插入一条数据
INSERT INTO deptBak VALUES(dept_seq.nextval,'MARKETAA','SSSHANGHAI');


--上机3
/*
===========================================================
|            在A_hr模式下创建dept表的公有同义词 p_sy_dept
============================================================
*/
--使用SYSTEM用户登录，A_hr获得创建公有同义词权限
GRANT CREATE PUBLIC SYNONYM TO A_hr;

--创建公有同义词p_sy_dept
CREATE PUBLIC SYNONYM p_sy_dept FOR dept;

--将查询dept表的权限授予public角色
GRANT SELECT ON dept TO public;

--使用A_od用户连接ORCL数据库
--在A_oe模式下访问公有同义词p_sy_dept
SELECT * FROM p_sy_dept;
  
  
  --上机4
/*
===========================================================
|              创建索引
============================================================
*/
--为客户表id创建唯一索引
CREATE UNIQUE INDEX index_unique_customer_id ON customers (customer_id);
--或者创建反向键索引
CREATE INDEX index_reverse_customer_id ON customers(customer_id) REVERSE;

--根据位图索引的特点为地域列创建位图索引
CREATE BITMAP INDEX index_bit_nls_territory ON customers(nls_territory);

--根据组合索引的特点为名和姓氏列创建组合索引
CREATE INDEX index_enames ON customers (cust_first_name,cust_last_name);


--上机5
/*
===========================================================
|              范围分区
============================================================
*/
CREATE TABLE rangeOrders
(
      order_id           NUMBER(12)
    , order_date         DATE NOT NULL
    , order_mode         VARCHAR2(8)
    , customer_id        NUMBER(6) NOT NULL
    , order_status       NUMBER(2)
    , order_total        NUMBER(8,2)
    , sales_rep_id       NUMBER(6)
    , promotion_id       NUMBER(6)
)

PARTITION BY RANGE (order_date)
(
   PARTITION Part1 VALUES LESS THAN (to_date('2005-01-01', 'yyyy-mm-dd')),
   PARTITION Part2 VALUES LESS THAN (to_date('2006-01-01', 'yyyy-mm-dd')),
   PARTITION Part3 VALUES LESS THAN (to_date('2007-01-01', 'yyyy-mm-dd')),
   PARTITION Part4 VALUES LESS THAN (to_date('2008-01-01', 'yyyy-mm-dd')),
   PARTITION Part5 VALUES LESS THAN (to_date('2009-01-01', 'yyyy-mm-dd')),
   PARTITION Part6 VALUES LESS THAN (maxvalue)
);

--要查看每一分区的数据
SELECT  *  FROM rangeOrders partition(Part1);
SELECT  *  FROM rangeOrders partition(Part2);
SELECT  *  FROM rangeOrders partition(Part3);
SELECT  *  FROM rangeOrders partition(Part4);
SELECT  *  FROM rangeOrders partition(Part5);

--插入'2013/01/01'数据
insert into rangeOrders values (1001,to_date('2013-01-01','yyyy-mm-dd'),'direct',101,0,1000,153,null);


--上机6
/*
===========================================================
|     利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表
============================================================
*/
--根据orders表创建间隔分区表 intervarOrders
CREATE TABLE intervalOrders
		PARTITION BY RANGE(order_date)
		INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
		(PARTITION P1 VALUES LESS THAN (to_date('2005-01-01','yyyy/mm/dd')))
        AS SELECT * FROM Orders;
        
--2.查询分区情况
SELECT table_name,partition_name 
FROM user_tab_partitions 
WHERE table_name=UPPER('intervalOrders');

--3.向表插入'2013/01/01'数据
insert into intervalOrders values (1001,to_date('2013-01-01','yyyy-mm-dd'),'direct',101,0,1000,153,null);


